﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient

Public Class NguoiDangDAO

    Public Shared Function GetAll() As List(Of NguoiDangDTO)
        Dim listNguoiDang As New List(Of NguoiDangDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectNguoidang"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim nguoidang As NguoiDangDTO = New NguoiDangDTO()
                nguoidang.strMaNguoiDang = rd.GetString(0)
                nguoidang.strHoTen = rd.GetString(1)
                nguoidang.strDiaChi = rd.GetString(2)
                nguoidang.strDienThoai = rd.GetString(3)
                nguoidang.strEmail = rd.GetString(4)

                listNguoiDang.Add(nguoidang)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNguoiDang
    End Function

    Public Shared Function ExportDS_ChuNha() As DataTable
        Dim listChuNha As New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "exportDS_ChuNha"
        cmd.Connection = cn

        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        da.Fill(listChuNha)

        Return listChuNha
    End Function

    Public Shared Function LayNguoiDangTamXoa() As List(Of NguoiDangDTO)
        Dim listNguoiDang As New List(Of NguoiDangDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectND_TamXoa"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim nguoidang As NguoiDangDTO = New NguoiDangDTO()
                nguoidang.strMaNguoiDang = rd.GetString(0)
                nguoidang.strHoTen = rd.GetString(1)
                nguoidang.strDiaChi = rd.GetString(2)
                nguoidang.strDienThoai = rd.GetString(3)
                nguoidang.strEmail = rd.GetString(4)

                listNguoiDang.Add(nguoidang)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNguoiDang
    End Function

    Public Shared Sub Insert(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_InsertNguoidang"
            cmd.Connection = cn
            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Delete(ByVal paraID As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_DeleteNguoidang"
            cmd.Connection = cn

            cmd.Parameters.Add(paraID)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update(ByVal sqlParameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateNguoidang"
            cmd.Connection = cn

            cmd.Parameters.AddRange(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub

    Public Shared Sub Update_TamXoa(ByVal parameters As SqlParameter())
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateNguoidangTamXoa"
            cmd.Connection = cn

            cmd.Parameters.AddRange(parameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

    End Sub


    Public Shared Function LayDSHoTen() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayDSHoTen"
            cmd.Connection = cn

            Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)

            da.Fill(dt)
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return dt
    End Function

    Public Shared Function LayMa_ND_Cuoi() As String
        Dim macuoi As String = ""

        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "LayMa_ND_Cuoi"
            cmd.Connection = cn

            cn.Open()
            macuoi = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return macuoi
    End Function

    Public Shared Function TimKiemTheoMa(ByVal paraID As SqlParameter) As List(Of NguoiDangDTO)
        Dim listNguoiDang As New List(Of NguoiDangDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemNguoidangTheoMa"
            cmd.Parameters.Add(paraID)
            cmd.Connection = cn

            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim nguoidang As NguoiDangDTO = New NguoiDangDTO()
                nguoidang.strMaNguoiDang = rd.GetString(0)
                nguoidang.strHoTen = rd.GetString(1)
                nguoidang.strDiaChi = rd.GetString(2)
                nguoidang.strDienThoai = rd.GetString(3)
                nguoidang.strEmail = rd.GetString(4)

                listNguoiDang.Add(nguoidang)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNguoiDang

    End Function

    Public Shared Function TimKiemTheoTen(ByVal ten As SqlParameter) As List(Of NguoiDangDTO)
        Dim listNguoiDang As New List(Of NguoiDangDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_TimKiemNguoidangTheoTen"
            cmd.Parameters.Add(ten)
            cmd.Connection = cn

            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()

            While rd.Read()
                Dim nguoidang As NguoiDangDTO = New NguoiDangDTO()
                nguoidang.strMaNguoiDang = rd.GetString(0)
                nguoidang.strHoTen = rd.GetString(1)
                nguoidang.strDiaChi = rd.GetString(2)
                nguoidang.strDienThoai = rd.GetString(3)
                nguoidang.strEmail = rd.GetString(4)

                listNguoiDang.Add(nguoidang)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listNguoiDang

    End Function
End Class

